package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.common.SystemException;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.SubFormSetDao;
import com.powerunion.datacollection.model.FieldItem;
import com.powerunion.datacollection.model.SubFormSet;
import com.powerunion.datacollection.model.SysServer;
import com.powerunion.datacollection.model.jsonObject.DBTable;
import com.powerunion.datacollection.model.jsonObject.DBTableField;
import com.powerunion.datacollection.model.jsonObject.TreeNode;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.datacollection.util.DBUtil;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class SubFormSetDaoImpl extends JdbcBase implements SubFormSetDao {   
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			SubFormSetDaoImpl.class);

	protected static final String sqlInsert4sub = "insert into dc_FORM_datasrc(form_id ,SVR_NAME,CONN_STR,TBL_NAME,KEY_FLD,key_fld_type,key_auto_flag,para_id,id) values(?,?,?,?,?,?,?,?,?)";
	protected static final String sqlUpdate4sub = "update dc_FORM_datasrc set form_id =?,SVR_NAME=?,CONN_STR=?,TBL_NAME=?,KEY_FLD=?,key_fld_type=?,key_auto_flag=?,para_id=? where id=?";
	protected static final String sqlDelete4Sub = "delete from dc_FORM_datasrc where form_id =?";
	protected static final String sqlGetByKey4MaintTbl = "select id,form_id ,SVR_NAME,CONN_STR,TBL_NAME,KEY_FLD,key_fld_type,key_auto_flag from dc_FORM_datasrc where form_id=? and para_id is null";
	protected static final String sqlGetByKey4SubTbl = "select s.id,s.form_id,s.SVR_NAME,s.CONN_STR,s.TBL_NAME,s.KEY_FLD,s.key_fld_type,s.key_auto_flag,s.para_id,(select m.TBL_NAME from dc_FORM_datasrc m where s.para_id=m.id)maint_tbl from " +
			"dc_FORM_datasrc s where s.form_id=? and s.para_id is not null ";
	private DBUtil dbUtil = null;
	
	public SubFormSetDaoImpl() {
		super();
		this.dbUtil = new DBUtil();
	}

	public boolean delete(String formId) throws DaoException{
		boolean isSuccess = false;
		UpdateOperator uo = new UpdateOperator();
		uo.setSql(sqlDelete4Sub);
		uo.addParameter(new SqlParameter(SqlType.VARCHAR, formId));

		try{
			uo.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("delete4SubTbl throw error!");
		}
	
		int efcount = uo.getEffectCounts();
		if (efcount > 0) {
			isSuccess = true;
		}

		return isSuccess;
	}

	public SubFormSet findFormSetByKey4MaintTbl(String formId) throws DaoException {
		SubFormSet fs = new SubFormSet();
		QueryOperator query = new QueryOperator();
		query.setSql(sqlGetByKey4MaintTbl);
		query.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
		try {
			query.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("查找ID为:" + formId + " 的主表FormSet信息失败！");
		}

		if (query.resultSetAvailable()) {//检查结果集是否可用
			//获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if (rs.rowCount > 0) {
				fs.setId(rs.getFieldValueAsString("id"));
				fs.setFsetId(rs.getFieldValueAsString("FSET_ID"));
				fs.setSvrName(rs.getFieldValueAsString("SVR_NAME"));
				fs.setConnStr(rs.getFieldValueAsString("CONN_STR"));
				fs.setTblName(rs.getFieldValueAsString("TBL_NAME"));
				fs.setKeyFld(rs.getFieldValueAsString("KEY_FLD"));
				fs.setKeyType(rs.getFieldValueAsString("key_fld_type"));
				int keyAutoFlag = rs.getFieldValueAsInteger("key_auto_flag") == null ? 0
						: rs.getFieldValueAsInteger("key_auto_flag").intValue();
				fs.setKeyAutoFlag(keyAutoFlag);

			}
			rs.clearAll();
		}
		return fs;
	}
	
	public SubFormSet findFormSetByKey4SubTbl(String formId) throws DaoException {
		SubFormSet fs = new SubFormSet();
		QueryOperator query = new QueryOperator();
		query.setSql(sqlGetByKey4SubTbl);
		query.addParameter(new SqlParameter(SqlType.VARCHAR, formId));
		try {
			query.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("查找ID为:" + formId + " 的从表FormSet信息失败！");
		}

		if (query.resultSetAvailable()) {//检查结果集是否可用
			//获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if (rs.rowCount > 0) {
				fs.setId(rs.getFieldValueAsString("id"));
				fs.setFsetId(rs.getFieldValueAsString("FSET_ID"));
				fs.setSvrName(rs.getFieldValueAsString("SVR_NAME"));
				fs.setConnStr(rs.getFieldValueAsString("CONN_STR"));
				fs.setTblName(rs.getFieldValueAsString("TBL_NAME"));
				fs.setKeyFld(rs.getFieldValueAsString("KEY_FLD"));
				fs.setKeyType(rs.getFieldValueAsString("key_fld_type"));
				int keyAutoFlag = rs.getFieldValueAsInteger("key_auto_flag") == null ? 0
						: rs.getFieldValueAsInteger("key_auto_flag").intValue();
				fs.setKeyAutoFlag(keyAutoFlag);
				SubFormSet ms = new SubFormSet();
				ms.setId(rs.getFieldValueAsString("para_id"));
				ms.setTblName(rs.getFieldValueAsString("maint_tbl"));
				fs.setMainTbl(ms);
			}
			rs.clearAll();
		}

		return fs;
	}

	public SysServer getSysServer(String syrName) throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		SysServer sysServer = null;
		try {
			conn = dbUtil.getConnection();
			StringBuffer sql = new StringBuffer();
			sql
					.append(
							"select svr_name,svr_desc,dsn_name,dbms_svr,db_name,odbc_drvr,")
					.append(
							"conn_user,conn_pwd,conn_str,conn_oth,port from sys_svr where svr_name=?");

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, syrName);
			rs = ps.executeQuery();
			sysServer = new SysServer();
			while (rs.next()) {
				sysServer.setConnOth(rs.getString("conn_oth"));
				sysServer.setConnPwd(rs.getString("conn_pwd"));
				sysServer.setConnStr(rs.getString("conn_str"));
				sysServer.setConnUser(rs.getString("conn_user"));
				sysServer.setDbmsSvr(rs.getString("dbms_svr"));
				sysServer.setDbName(rs.getString("db_name"));
				sysServer.setDsnName(rs.getString("dsn_name"));
				sysServer.setOdbcDrvr(rs.getString("odbc_drvr"));
				sysServer.setPort(rs.getString("port"));
				sysServer.setSvrDesc(rs.getString("svr_desc"));
				sysServer.setSvrName(rs.getString("svr_name"));
			}
			return sysServer;

		} catch (Exception e) {
			logger.error("getSysServer of throw :", e);
			throw new Exception("获取SysServer服务器对象信息出错！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}
	}

	private List getTables(SysServer sysServer) throws Exception {
		// 参数为空
		if (sysServer == null) {
			String[] args = { "sysServer" };
			// 打印错误日志
			StringBuffer msglog = new StringBuffer(
					"empty invoke parameter sysServer NULL ");
			logger.error(msglog.toString());
			throw new SystemException("error.common.parameter.empty", args);
		}

		Connection conn = null;
		DatabaseMetaData databaseMetaData = null;
		ResultSet tableSet = null;
		List tableList = null;
		try {
			StringBuffer url = new StringBuffer();
			url.append("jdbc:oracle:thin:@").append(sysServer.getDbmsSvr())
					.append(":").append(sysServer.getPort()).append(":")
					.append(sysServer.getDbName());

			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url.toString(), sysServer
					.getConnUser(), sysServer.getConnPwd());

			databaseMetaData = conn.getMetaData();
			tableSet = databaseMetaData.getTables(null, sysServer.getConnUser()
					.toUpperCase(), "%", new String[] { "TABLE" });

			tableList = new ArrayList();
			while (tableSet.next()) {
				TreeNode treeNode = new TreeNode();
				String tableName = tableSet.getString("TABLE_NAME");
				treeNode.setId(tableName);
				treeNode.setText(tableName);
				treeNode.setLeaf(true);
				treeNode.setExpandable(false);
				treeNode.setType("1");
				tableList.add(treeNode);
			}
		} catch (Exception e) {
			logger.error("getSysServer of throw :", e);
			throw new Exception("获取SysServer服务器对象信息出错！");
		} finally {
			DBUtils.close(conn);
			DBUtils.close(tableSet);
		}
		return tableList;
	}

	public List getFields(String syrName, String tableName) throws Exception {
		// 参数为空
		if (StringUtils.isEmpty(syrName)) {
			String[] args = { "syrName" };
			// 打印错误日志
			StringBuffer msglog = new StringBuffer(
					"empty invoke parameter syrName NULL ");
			logger.error(msglog.toString());
			throw new SystemException("error.common.parameter.empty", args);
		}
		// 参数为空
		if (StringUtils.isEmpty(tableName)) {
			String[] args = { "tableName" };
			// 打印错误日志
			StringBuffer msglog = new StringBuffer(
					"empty invoke parameter tableName NULL ");
			logger.error(msglog.toString());
			throw new SystemException("error.common.parameter.empty", args);
		}

		Connection conn = null;
		DatabaseMetaData databaseMetaData = null;
		ResultSet columnSet = null;
		List columnList = null;
		try {
			SysServer sysServer = new SysServer();
			sysServer = this.getSysServer(syrName);
			StringBuffer url = new StringBuffer();
			url.append("jdbc:oracle:thin:@").append(sysServer.getDbmsSvr())
					.append(":").append(sysServer.getPort()).append(":")
					.append(sysServer.getDbName());

			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url.toString(), sysServer
					.getConnUser(), sysServer.getConnPwd());

			databaseMetaData = conn.getMetaData();
			columnSet = databaseMetaData.getColumns(null, sysServer
					.getConnUser().toUpperCase(), tableName.toUpperCase(), "%");

			columnList = new ArrayList();
			while (columnSet.next()) {
				DBTableField dtf = new DBTableField();
				dtf.setFieldName(columnSet.getString("COLUMN_NAME"));
				dtf.setFieldType(columnSet.getString("TYPE_NAME"));

				columnList.add(dtf);
			}

		} catch (Exception e) {
			logger.error("getFieldNames of throw :", e);
			throw new Exception("获取表名为:" + tableName + "的所有字段名称信息出错！");
		} finally {
			DBUtils.close(conn);
			DBUtils.close(columnSet);
		}
		return columnList;
	}

	/**
	 * 获取服务器对应的表信息 
	 * 主要用于主表报送区字段设计的下拉框内容设置
	 * @param svrName
	 * @return
	 * @throws Exception
	 */
	public List getServerTables(String svrName) throws DaoException {

		Connection conn = null;
		DatabaseMetaData databaseMetaData = null;
		ResultSet tableSet = null;
		List tableList = null;

		try {
			SysServer sysServer = this.getSysServer(svrName);

			StringBuffer url = new StringBuffer();
			url.append("jdbc:oracle:thin:@").append(sysServer.getDbmsSvr())
					.append(":").append(sysServer.getPort()).append(":")
					.append(sysServer.getDbName());

			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url.toString(), sysServer
					.getConnUser(), sysServer.getConnPwd());

			databaseMetaData = conn.getMetaData();
			tableSet = databaseMetaData.getTables(null, sysServer.getConnUser()
					.toUpperCase(), "%", new String[] { "TABLE" });

			tableList = new ArrayList();
			while (tableSet.next()) {
				DBTable table = new DBTable();
				String tableName = tableSet.getString("TABLE_NAME");
				table.setTableName(tableName);
				String tableDesc = this.getTableDesc(tableName, svrName, conn);
				table.setTableDesc(tableDesc);

				tableList.add(table);
			}
		} catch (Exception e) {
			logger.error("getSysServer of throw :", e);
			throw new DaoException("获取 " + svrName + "服务器对应的表信息出错！");
		} finally {
			DBUtils.close(conn);
			DBUtils.close(tableSet);
		}
		return tableList;
	}

	private String getTableDesc(String tablName, String svrName, Connection conn)
			throws DaoException {
		String sql = "select tbl_desc from tbl_desc where tbl_name = ? and svr_name=? ";
		QueryOperator query = new QueryOperator();
		query.setSql(sql);
		query.setUseOnlyConnectionFlag(true);
		query.setPresentConnection(conn);
		String tabDesc = "";
		try {
			query.addParameter(new SqlParameter(SqlType.VARCHAR, tablName));
			query.addParameter(new SqlParameter(SqlType.VARCHAR, svrName));
			query.access();
			if (query.resultSetAvailable()) {
				//获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if (rs.rowCount > 0) {
					tabDesc = rs.getFieldValueAsString("tbl_desc");
				}

				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("查找服务器出错！");
		}
		return tabDesc;
	}

	public List getComboFields(FieldItem item) throws DaoException {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List fields = null;
		try {
			SysServer sysServer = new SysServer();
			sysServer = this.getSysServer(item.getSvrName());
			StringBuffer url = new StringBuffer();
			url.append("jdbc:oracle:thin:@").append(sysServer.getDbmsSvr())
					.append(":").append(sysServer.getPort()).append(":")
					.append(sysServer.getDbName());

			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url.toString(), sysServer
					.getConnUser(), sysServer.getConnPwd());

			fields = new ArrayList();
			StringBuffer sql = new StringBuffer();
			sql.append("select ").append(item.getItemLabel() + ",").append(
					item.getItemValue()).append(" from ").append(
					item.getTblName());

			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				FieldItem fi = new FieldItem();
				fi.setItemLabel(rs.getString(item.getItemLabel()));
				fi.setItemValue(rs.getString(item.getItemValue()));

				fields.add(fi);
			}

		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getComboFields throw of error！");
		} finally {
			DBUtils.close(rs, ps, conn);

		}
		return fields;

	}

	private DBTableField getTableKeyInfo(String tablName, String svrName,
			Connection conn) throws DaoException {
		//参数为空
		if (StringUtils.isEmpty(tablName)) {
			String[] args = { "tablName" };
			// 打印错误日志
			StringBuffer msglog = new StringBuffer(
					"empty invoke parameter tablName NULL ");
			logger.error(msglog.toString());
			throw new SystemException("error.common.parameter.empty", args);
		}
		//参数为空
		if (StringUtils.isEmpty(svrName)) {
			String[] args = { "svrName" };
			// 打印错误日志
			StringBuffer msglog = new StringBuffer(
					"empty invoke parameter svrName NULL ");
			logger.error(msglog.toString());
			throw new SystemException("error.common.parameter.empty", args);
		}
		DatabaseMetaData databaseMetaData = null;
		DBTableField keyField = new DBTableField();
		Connection conn2 = null;
		try {
			if (conn == null) {
				SysServer sysServer = new SysServer();
				sysServer = this.getSysServer(svrName);
				StringBuffer url = new StringBuffer();
				url.append("jdbc:oracle:thin:@").append(sysServer.getDbmsSvr())
						.append(":").append(sysServer.getPort()).append(":")
						.append(sysServer.getDbName());

				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn2 = DriverManager.getConnection(url.toString(), sysServer
						.getConnUser(), sysServer.getConnPwd());
				databaseMetaData = conn2.getMetaData();
			} else {
				databaseMetaData = conn.getMetaData();
			}
			ResultSet pk = databaseMetaData.getBestRowIdentifier(null, null,
					tablName.toUpperCase(), 1, true);

			while (pk.next()) {//这里得用while而不是if
				keyField.setFieldName(pk.getString(2));
				keyField.setFieldType(pk.getString(4));
			}

		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getTableKeyInfo throw error!");
		} finally {
			if (conn == null) {
				DBUtils.close(conn2);
			}
		}
		return keyField;

	}

	public JSONArray getSubFormSetDataSourceTree(String formId,String paraSeq)
			throws Exception {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List treeNodeArray = new ArrayList();

		try {

			if (paraSeq == null || paraSeq.equals("null")) {
				List treeNodeList = new ArrayList();
				String sqlString = "";

				sqlString = "select svr_name from dc_FORM_datasrc where form_ID = ? and para_id is null ";
				conn = dbUtil.getConnection();
				ps = conn.prepareStatement(sqlString);
				ps.setString(1, formId);
				rs = ps.executeQuery();

				while (rs.next()) {
					TreeNode treeNode = new TreeNode();
					treeNode.setId(rs.getString("svr_name"));
					treeNode.setText(rs.getString("svr_name"));
					treeNode.setIcon("../js/images/database.gif");
					treeNode.setLeaf(false);
					treeNode.setExpandable(false);
					treeNode.setType("0");

					treeNodeList.add(treeNode);
				}
				treeNodeArray = treeNodeList;
			} else {
				SysServer sysServer = this.getSysServer(paraSeq);
				treeNodeArray = this.getTables(sysServer);
			}
			
			logger.debug(JSONArray.fromObject(treeNodeArray));
			//得到JSON数组
			JSONArray jsonarr = JSONArray.fromObject(treeNodeArray);
		
			//返回JSON数据
			return jsonarr;
		} catch (Exception e) {
			logger.error("", e);
			throw new Exception("getMainFormSetDataSourceTree throws error! ");
		} finally {
			DBUtils.close(rs, ps, conn);
		}
	}
	
	public boolean save(SubFormSet formSet) throws DaoException {
		boolean isSuccess = false;
		UpdateOperator uo = new UpdateOperator();
		if(StringUtils.isEmpty(formSet.getId())){
			uo.setSql(sqlInsert4sub);
			String id = String.valueOf(this.getSeq("dc_FORM_datasrc"));
			formSet.setId(id);
		}else{
			uo.setSql(sqlUpdate4sub);
		}
		
		uo.addParameter(new SqlParameter(SqlType.VARCHAR, formSet.getFsetId()));
		uo
				.addParameter(new SqlParameter(SqlType.VARCHAR, formSet
						.getSvrName()));
		uo
				.addParameter(new SqlParameter(SqlType.VARCHAR, formSet
						.getConnStr()));
		uo
		.addParameter(new SqlParameter(SqlType.VARCHAR, formSet.getTblName()));
		//获取主键字段名及类型
		DBTableField dtf = this.getTableKeyInfo(formSet.getTblName(), formSet
				.getSvrName(), null);
		uo.addParameter(new SqlParameter(SqlType.VARCHAR, dtf.getFieldName()));
		uo.addParameter(new SqlParameter(SqlType.VARCHAR, dtf.getFieldType()));
		uo.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(formSet
				.getKeyAutoFlag())));
		if(formSet.getMainTbl() == null || StringUtils.isEmpty(formSet.getMainTbl().getId())){
			uo.addParameter(new SqlParameter(SqlType.VARCHAR, null));
		}else{
			uo.addParameter(new SqlParameter(SqlType.VARCHAR, formSet.getMainTbl().getId()));
		}
		uo.addParameter(new SqlParameter(SqlType.VARCHAR, formSet.getId()));
		
		try {
			uo.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("保存主表信息失败！");
		}

		int effectCount = uo.getEffectCounts();
		if (effectCount > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "主表信息"
						+ Constants.SUCCESS);
			}
			isSuccess = true;
			return isSuccess;
		} else {
			if (logger.isDebugEnabled()) {
				logger
						.debug("save:" + Constants.SAVE + "主表信息"
								+ Constants.FAIL);
			}
			return isSuccess;
		}
	}
	
	
}
